package com.hnkywl.sts.dao.cwgl.pjdr;

import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: ccr
 * Date: 13-6-6
 * Time: 上午10:24
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class WlfpdrDao extends SimpleHibernateTemplate{
    /**
     * 获得凭证主信息
     * @return
     */
    public Map findVoucherMain(){
        String sql = "select p.name  as \"vchrtypecode\", yg.xm   as \"createuser\", sysdate as \"createdate\"" +
                " from t_sys_pzz p, t_sys_yg yg where p.deleted = 0 and yg.deleted = 0 and p.code = 2 and yg.id = ?";
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        sqlQuery.setLong(0, YgService.getCurrentYg().getId());
        return (Map) sqlQuery.uniqueResult();
    }

    /**
     * 分录
     * @param jsdId
     * @return
     */
    public List<Map> findVoucherItem(Long jsdId){
        String sql = "select t.*,rownum as \"itemorder\" from (" + getFl1Sql() + " union all "
                + getFl2Sql() + " union all " + getFl3Sql() + ") t";
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        sqlQuery.setLong("jsdId",jsdId);
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }

    private String getFl1Sql(){
        String sql = "select '应付'||gsjg.qc||'运输费' as \"digest\",cykm.jqcwkmmc as \"subjectname\",cykm.kmdm as \"subjectcode\"," +
                "       nvl(glfy.me,0) - nvl(glfy.se,0)  as \"debit\" ,0 as \"credit\",'wlskmdm' as \"suppliercode\",0 as \"qty\"" +
                " from t_sys_cykmdmys cykm" +
                "  left join t_sys_pm_cy pmcy on(cykm.codeid = pmcy.codeid)" +
                "  left join t_sys_gg gg on(gg.pm_id = pmcy.pm_id)" +
                "  left join t_biz_cgdmx cgdmx on(cgdmx.gg_id = gg.id)" +
                "  left join t_biz_cgd cgd on(cgdmx.cgd_id = cgd.id)" +
                "  left join t_biz_glfy glfy on(cgd.id = glfy.cgd_id)" +
                "  left join t_biz_jsd jsd on (jsd.id = glfy.wljsd_id)" +
                "  left join t_sys_gsjg gsjg on (gsjg.id = jsd.gs_id)" +
                " where jsd.id = :jsdId  and cykm.gsid = gsjg.id and glfy.xsd_id is null" +
                " union all" +
                " select '应付'||gsjg.qc||'运输费' as \"digest\",cykm.jqcwkmmc as \"subjectname\",cykm.kmdm as \"subjectcode\"," +
                "       nvl(glfy.me,0) - nvl(glfy.se,0)  as \"debit\" ,0 as \"credit\",'' as \"suppliercode\",0 as \"qty\"" +
                " from t_sys_cykmdmys cykm" +
                "  left join t_sys_pm_cy pmcy on(cykm.codeid = pmcy.codeid)" +
                "  left join t_sys_gg gg on(gg.pm_id = pmcy.pm_id)" +
                "  left join t_biz_kh kh on(kh.jtwz_id = gg.id)" +
                "  left join t_biz_xsmx xsdmx on(xsdmx.kh_id = kh.id)" +
                "  left join t_biz_xsd xsd on(xsdmx.xsd_id = xsd.id)" +
                "  left join t_biz_glfy glfy on(xsd.id = glfy.xsd_id)" +
                "  left join t_biz_jsd jsd on (jsd.id = glfy.wljsd_id)" +
                "  left join t_sys_gsjg gsjg on (gsjg.id = jsd.gs_id)" +
                " where jsd.id = :jsdId  and cykm.gsid = gsjg.id and glfy.cgd_id is null";
        return sql;
    }
    private  String getFl2Sql(){
        String sql = "select '应付'||gs.qc||'运输费' as \"digest\",cykm.jqcwkmmc as \"subjectname\",cykm.kmdm as \"subjectcode\"," +
                "       sum(nvl(fy.se,0)) as \"debit\" ,0 as \"credit\",'' as \"suppliercode\",0 as \"qty\"" +
                "  from t_biz_glfy fy" +
                "  left join t_biz_jsd jsd on (jsd.id = fy.wljsd_id)" +
                "  left join t_sys_cyfyys cyfy on(fy.cyfy_fylb_id = cyfy.id)" +
                "  left join t_sys_cykmdmys cykm on (cyfy.cykmdmys_id = cykm.id)" +
                "  left join t_sys_gsjg gs on (gs.id = jsd.gs_id)" +
                " where jsd.id = :jsdId group by cykm.kmdm,cykm.jqcwkmmc,gs.qc";
        return sql;
    }
    private  String getFl3Sql(){
        String sql = "select '应付'||gs.qc||'运输费' as \"digest\",cyfy.fykmqc as \"subjectname\",cyfy.kmdm as \"subjectcode\"," +
                "       0 as \"debit\" ,sum(nvl(fy.me,0)) as \"credit\",'wlskmdm' as \"suppliercode\",0 as \"qty\"" +
                "  from t_biz_glfy fy" +
                "  left join t_biz_jsd jsd on (jsd.id = fy.wljsd_id)" +
                "  left join t_sys_cyfyys cyfy on(fy.cyfy_fylb_id = cyfy.id)" +
                "  left join t_sys_gsjg gs on (gs.id = jsd.gs_id)" +
                " where jsd.id = :jsdId group by cyfy.id,gs.qc,cyfy.kmdm,cyfy.fykmqc";
        return sql;
    }

}
